package com.bank.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.bank.model.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.util.*;


@Service
public class CRUD {

    @Autowired
    DruidDataSource dataSource;

    @Autowired
    SnowflakeIdWorker snowflakeIdWorker;

    /*
    * 查
    * */


    /**
     * 通过手机号查找uid
     * @param phoneNumber
     * @return 找到uid,找不到""
     */
    public String findUidByPhoneNumber(String phoneNumber){
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select u_id from t_user where u_phone=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,phoneNumber);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                String result = resultSet.getString(1);
                connection.close();
                return result;
            }else{
                connection.close();
                return "";
            }
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return "";
    }

    /**
     * 通过身份证查找uid
     * @param idCard
     * @return 找到uid,找不到""
     */
    public String findUidByIdCard(String idCard){
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select u_id from t_user where u_id_card=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,idCard);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                String result = resultSet.getString(1);
                connection.close();
                return result;
            }else{
                connection.close();
                return "";
            }
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return "";
    }

    /**
     * 通过uid查密码
     * @param uid
     * @return 找到psd找不到""
     */
    public String findPasswordByUid(String uid){
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select u_password from t_user where u_id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,uid);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                String result = resultSet.getString(1);
                connection.close();
                return result;
            }else{
                connection.close();
                return "";
            }
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return "";
    }


    /**
     * 通过email查询uid
     * @param email
     * @return uid
     */
    public String findUidByEmail(String email){
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select u_password from t_user where u_mail=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,email);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                String result = resultSet.getString(1);
                connection.close();
                return result;
            }else{
                connection.close();
                return "";
            }
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return "";
    }


    /**
     * 通过uid生成user类
     * @param uid
     * @return User类
     */
    public User getUserByUid(String uid){
        String uId = uid,name,idCard,phoneNumber,email;
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select * from t_user where u_id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,uid);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                name = resultSet.getString(2);
                phoneNumber = resultSet.getString(3);
                idCard = resultSet.getString(4);
                email = resultSet.getString(5);
                connection.close();
                return new User(uId,name,idCard,phoneNumber,email);
            }else{
                connection.close();
                return null;
            }
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 通过uid获取用户信息
     * @param uid
     * @return 用户所有信息
     */
    public Map<String,String> getUserInfo(String uid){
        Map<String,String> map = new HashMap<>();
        String uId = uid,name,idCard,phoneNumber,email;
        map.put("uid",uid);
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select * from t_user where u_id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,uid);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                name = resultSet.getString(2);
                map.put("name",name);
                phoneNumber = resultSet.getString(3);
                map.put("phone",phoneNumber);
                idCard = resultSet.getString(4);
                map.put("idCard",idCard);
                email = resultSet.getString(5);
                map.put("email",email);
            }
            connection.close();
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 获取uid用户的所有股票资产
     * @param uid
     * @return
     */
    public List<Assets> getStockAssetsByUid(String uid){
        List<Assets> ret = new ArrayList<>();
        String aid,price,income,date,num,product,type;
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select t_stock_assets.*,t_stock.st_name " +
                    "from t_stock_assets,t_stock " +
                    "where t_stock_assets.sta_stock_id=t_stock.st_id and t_stock_assets.sta_user_id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,uid);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                aid = resultSet.getString(1);
                num = resultSet.getBigDecimal(2).toString();
                price = resultSet.getBigDecimal(3).toString();
                income = resultSet.getBigDecimal(4).toString();
                date = resultSet.getTimestamp(5).toString();
                product = resultSet.getString(8);
                type = "股票";
                ret.add(new Assets(aid,price,income,date,num,product,type));
            }
            connection.close();
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return ret;
    }


    /**
     * 获取uid用户的所有基金资产
     * @param uid
     * @return
     */
    public List<Assets> getFundAssetsByUid(String uid){
        List<Assets> ret = new ArrayList<>();
        String aid,price,income,date,num,product,type;
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select t_fund_assets.*,t_fund.fu_name " +
                    "from t_fund_assets,t_fund " +
                    "where t_fund_assets.fua_fund_id=t_fund.fu_id and t_fund_assets.fua_user_id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,uid);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                aid = resultSet.getString(1);
                price = resultSet.getBigDecimal(2).toString();
                income = resultSet.getBigDecimal(3).toString();
                date = resultSet.getTimestamp(4).toString();
                num = resultSet.getBigDecimal(6).toString();
                product = resultSet.getString(8);
                type = "基金";
                ret.add(new Assets(aid,price,income,date,num,product,type));
            }
            connection.close();
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return ret;
    }

    /**
     * 获取uid用户的所有保险资产
     * @param uid
     * @return
     */
    public List<Assets> getInsuranceAssetsByUid(String uid){
        List<Assets> ret = new ArrayList<>();
        String aid,price,income,date,num,product,type;
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select t_insurance_assets.*,t_insurance.in_name " +
                    "from t_insurance_assets,t_insurance " +
                    "where t_insurance_assets.ina_insurance_id=t_insurance.in_id and t_insurance_assets.ina_user_id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,uid);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                aid = resultSet.getString(1);
                price = resultSet.getBigDecimal(2).toString();
                num = resultSet.getBigDecimal(3).toString();
                income = resultSet.getBigDecimal(4).toString();
                date = resultSet.getTimestamp(5).toString();
                product = resultSet.getString(8);
                type = "保险";
                ret.add(new Assets(aid,price,income,date,num,product,type));
            }
            connection.close();
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return ret;
    }

    /**
     * 获取uid用户的所有的资产
     * @param uid
     * @return
     */
    public List<Assets> getAllAssetsByUid(String uid){
        List<Assets> ret = new ArrayList<>();
        ret.addAll(getStockAssetsByUid(uid));
        ret.addAll(getFundAssetsByUid(uid));
        ret.addAll(getInsuranceAssetsByUid(uid));
        Collections.sort(ret);
//        return ret.subList((startPage-1)*eachPageCount,startPage*eachPageCount-1);
        return ret;
    }

    /**
     * 获取所有在售的股票
     * @return
     */
    public List<Stock> getAllSaledStocksInfo(){
        List<Stock> ret = new ArrayList<>();
        String sid,name,des,price,risk,type;
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select * from t_stock where st_status=1";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                sid = resultSet.getString(1);
                name = resultSet.getString(2);
                des = resultSet.getString(3);
                price = resultSet.getBigDecimal(4).toString();
                risk = resultSet.getString(6);
                type = resultSet.getString(7);
                ret.add(new Stock(sid,name,des,price,risk,type));
            }
            connection.close();
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return ret;
    }

    /**
     * 获取所有在售的基金
     * @return
     */
    public List<Fund> getAllSaledFundsInfo(){
        List<Fund> ret = new ArrayList<>();
        String fid, name, des, price, risk, manager, type;
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select * from t_fund where fu_status=1";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                fid = resultSet.getString(1);
                name = resultSet.getString(2);
                type = resultSet.getString(3);
                risk = resultSet.getString(4);
                manager = resultSet.getString(5);
                des = resultSet.getString(7);
                price = resultSet.getBigDecimal(8).toString();
                ret.add(new Fund(fid,name,des,price,risk,manager,type));
            }
            connection.close();
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return ret;
    }

    /**
     * 获取所有在售的保险
     * @return
     */
    public List<Insurance> getAllSaledInsurancesInfo(){
        List<Insurance> ret = new ArrayList<>();
        String iid, name, des, price, type;
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select * from t_insurance where in_status=1";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                iid = resultSet.getString(1);
                name = resultSet.getString(2);
                price = resultSet.getBigDecimal(3).toString();
                des = resultSet.getString(4);
                type = resultSet.getString(5);
                ret.add(new Insurance(iid,name,des,price,type));
            }
            connection.close();
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return ret;
    }


    /**
     * 获取uid用户的所有银行卡
     * @param uid
     * @return
     */
    public List<Card> getAllCardsByUid(String uid){
        List<Card> ret = new ArrayList<>();
        String cid,type,balance,issueDate,limit;
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select * from t_card where c_user_id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,uid);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                cid = resultSet.getString(1);
                type = resultSet.getInt(2) == 0 ? "信用卡":"储蓄卡";
                balance = resultSet.getBigDecimal(3).toString();
                issueDate = resultSet.getDate(5).toString();
                limit = resultSet.getString(7);
                ret.add(new Card(cid,type,balance,issueDate,limit));
            }
            connection.close();
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return ret;
    }

    /**
     * 获取uid银行卡的数量
     * @param uid
     * @return
     */
    public int getCardsCountByUid(String uid){
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select COUNT(*) from t_card where c_user_id=? and c_type=1";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,uid);
            ResultSet resultSet = preparedStatement.executeQuery();

            if(resultSet.next()){
                int result = resultSet.getInt(1);
                connection.close();
                return result;
            }else{
                connection.close();
                return -1;
            }
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return -1;
    }

    /**
     * 获取uid用户的所有交易
     * @param uid
     */
    public List<Transaction> getAllTransactionsByUid(String uid){
        List<Transaction> ret = new ArrayList<>();
        String tid, amount, time, cardId;
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select t_transaction.* from t_transaction,t_card " +
                    "where t_transaction.tra_card_id=t_card.c_id and t_card.c_user_id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,uid);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                tid = resultSet.getString(1);
                amount = resultSet.getBigDecimal(2).toString();
                time = resultSet.getTimestamp(3).toString().split("\\.")[0];
                cardId = resultSet.getString(4);
                ret.add(new Transaction(tid,amount,time,cardId));
            }
            connection.close();
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        return ret;
    }


    /*
    * 增
    * */

    /**
     * 新增用户
     * @param uid
     * @param name
     * @param phone
     * @param idCard
     * @param mail
     * @param password
     */
    public void addNewUser(String uid,String name,String phone,String idCard,String mail,String password){
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "insert into t_user values(?,?,?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,uid);
            preparedStatement.setString(2,name);
            preparedStatement.setString(3,phone);
            preparedStatement.setString(4,idCard);
            if(mail.equals("@")) preparedStatement.setNull(5, Types.VARCHAR);
            else preparedStatement.setString(5,mail);
            preparedStatement.setString(6,password);
            preparedStatement.execute();
            connection.close();
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
    }


    /**
     * 新增银行卡
     * @param cardId
     * @param type
     * @param psd
     * @param date
     * @param uid
     * @param limit
     */
    public void addNewCard(String cardId, int type, String psd, Date date, String uid, BigDecimal limit){
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String sql = "insert into t_card values(?,?,0,?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,cardId);
            preparedStatement.setInt(2,type);
            preparedStatement.setString(3,psd);
            preparedStatement.setDate(4, date);
            preparedStatement.setString(5,uid);
            preparedStatement.setBigDecimal(6,limit);
            preparedStatement.execute();
            connection.close();
        }catch (Exception e){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
    }


    /**
     * 购买理财产品
     * @param uid
     * @param pid
     * @param count
     * @param price
     * @param payCardId
     * @return
     */
    public ReturnMessage buyProduct(String uid,String pid, BigDecimal count, BigDecimal price, String payCardId){
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            String user_sql = "select u_id from t_user where u_id=?";
            PreparedStatement preparedStatement1 = connection.prepareStatement(user_sql);
            preparedStatement1.setString(1,uid);
            ResultSet resultSet1 = preparedStatement1.executeQuery();
            //检查用户是否存在
            if(!resultSet1.next()){
                connection.rollback();
                return new ReturnMessage(0,"该用户不存在");
            }
            //检查该银行卡是否属于该用户
            String carad_sql = "select c_balance,c_limit from t_card where c_user_id=? and c_id=?";
            PreparedStatement preparedStatement2 = connection.prepareStatement(carad_sql);
            preparedStatement2.setString(1,uid);
            preparedStatement2.setString(2,payCardId);
            ResultSet resultSet2 = preparedStatement2.executeQuery();
            if(!resultSet2.next()){
                connection.rollback();
                return new ReturnMessage(0,"该用户没有该银行卡");
            }

            BigDecimal amount = count.multiply(price);
            BigDecimal balance = resultSet2.getBigDecimal(1);
            BigDecimal limit = resultSet2.getBigDecimal(2);
            BigDecimal money = balance.add(limit);

            if(money.compareTo(amount) == -1){
                //钱不够
                connection.rollback();
                if(limit.compareTo(new BigDecimal("0")) == 0) return new ReturnMessage(0,"储蓄卡卡余额不足");
                else return new ReturnMessage(0,"信用卡额度不足");
            }else{
                //钱够了
                //扣钱
                String money_sql = "update t_card set c_balance=? where c_user_id=? and c_id=?";
                PreparedStatement preparedStatement3 = connection.prepareStatement(money_sql);
                preparedStatement3.setBigDecimal(1,balance.subtract(amount));
                preparedStatement3.setString(2,uid);
                preparedStatement3.setString(3,payCardId);
                preparedStatement3.executeUpdate();

                //增加资产
                String new_assets_id = snowflakeIdWorker.nextId() + "";
                Timestamp time = new Timestamp(System.currentTimeMillis());
                switch (pid.substring(18)){
                    case "11":
                        //股票
                        String stock_sql = "insert into t_stock_assets values(?,?,?,0,?,?,?)";
                        PreparedStatement preparedStatement4 = connection.prepareStatement(stock_sql);
                        preparedStatement4.setString(1,new_assets_id + "66");
                        preparedStatement4.setBigDecimal(2,count);
                        preparedStatement4.setBigDecimal(3,price);
                        preparedStatement4.setTimestamp(4,time);
                        preparedStatement4.setString(5,uid);
                        preparedStatement4.setString(6,pid);
                        preparedStatement4.execute();
                        break;
                    case "22":
                        //基金
                        String fund_sql = "insert into t_fund_assets values(?,?,0,?,?,?,?)";
                        PreparedStatement preparedStatement5 = connection.prepareStatement(fund_sql);
                        preparedStatement5.setString(1,new_assets_id + "77");
                        preparedStatement5.setBigDecimal(2,price);
                        preparedStatement5.setTimestamp(3,time);
                        preparedStatement5.setString(4,uid);
                        preparedStatement5.setBigDecimal(5,count);
                        preparedStatement5.setString(6,pid);
                        preparedStatement5.execute();
                        break;
                    case "33":
                        //保险
                        String ins_sql = "insert into t_insurance_assets values(?,?,?,0,?,?,?)";
                        PreparedStatement preparedStatement6 = connection.prepareStatement(ins_sql);
                        preparedStatement6.setString(1,new_assets_id + "88");
                        preparedStatement6.setBigDecimal(2,price);
                        preparedStatement6.setBigDecimal(3,count);
                        preparedStatement6.setTimestamp(4,time);
                        preparedStatement6.setString(5,uid);
                        preparedStatement6.setString(6,pid);
                        preparedStatement6.execute();
                        break;
                    default:
                        connection.rollback();
                        return new ReturnMessage(0,"产品ID错误");
                }

                //增加交易记录
                String new_tra_id = snowflakeIdWorker.nextId() + "55";
                String tra_sql = "insert into t_transaction values(?,?,?,?)";
                PreparedStatement preparedStatement7 = connection.prepareStatement(tra_sql);
                preparedStatement7.setString(1,new_tra_id);
                preparedStatement7.setBigDecimal(2,amount.negate());
                preparedStatement7.setTimestamp(3,time);
                preparedStatement7.setString(4,payCardId);
                preparedStatement7.execute();
            }

            connection.commit();
            connection.close();
            return new ReturnMessage(1,"购买成功");

        }catch (Exception e){
            try {
                connection.rollback();
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();

            return new ReturnMessage(0,"购买失败");
        }

    }






}
